/*     */ package com.newcrm.db;
/*     */ 
/*     */ import com.newcrm.exception.DBException;
/*     */ import java.sql.Connection;
/*     */ import java.sql.SQLException;
/*     */ import java.util.ArrayList;
/*     */ import java.util.Date;
/*     */ import java.util.List;
/*     */ import java.util.Map;
/*     */ import org.apache.commons.dbutils.DbUtils;
/*     */ import org.apache.commons.dbutils.GenKeyQueryRunner;
/*     */ import org.apache.commons.dbutils.MyMapHandler;
/*     */ import org.apache.commons.dbutils.MyMapListHandler;
/*     */ import org.apache.commons.dbutils.QueryRunner;
/*     */ import org.apache.commons.dbutils.handlers.ScalarHandler;
/*     */ import org.apache.log4j.Logger;
/*     */ 
/*     */ public class NoteManager
/*     */ {
/*  23 */   private static Logger logger = Logger.getLogger(NoteManager.class.getName());
/*     */ 
/*     */   public int add(int subjectId, String subjectName, int subjectType, String attachFiles, Date noteDate, Date nextContactDate, String remark, int noteVisible, String permissionsId, int parentCustomerId, int companyId, String userId, String userName, int dealId, String dealName)
/*     */   {
/*  52 */     if (subjectId <= 0) {
/*  53 */       return -1;
/*     */     }
/*  55 */     Connection conn = ConnectionManager.getInstance().getConnection();
/*  56 */     if (conn == null) {
/*  57 */       return -1;
/*     */     }
/*     */     try
/*     */     {
/*  61 */       GenKeyQueryRunner runner = new GenKeyQueryRunner(
/*  62 */         new ScalarHandler(), new String[] { "NoteId" });
/*  63 */       runner.update(conn, "INSERT INTO T_Note_Info (NoteType,SubjectId,SubjectName,SubjectType,AttachFiles,NoteDate,NextContactDate,Remark,NoteVisible,PermissionsId,CompanyId,DealId,DealName,CreateUserId,UpdateUserId,CreateDate,UpdateDate) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,NOW(),NOW())", new Object[] { Integer.valueOf(2), 
/*  64 */         Integer.valueOf(subjectId), subjectName, Integer.valueOf(subjectType), attachFiles, noteDate, 
/*  65 */         nextContactDate, remark, Integer.valueOf(noteVisible), permissionsId, 
/*  66 */         Integer.valueOf(companyId), Integer.valueOf(dealId), dealName, userId, userId });
/*     */ 
/*  68 */       int id = ((Long)runner.getGeneratedKeys()).intValue();
/*     */ 
/*  70 */       if ((subjectType == 0) || 
/*  71 */         (subjectType == 1))
/*     */       {
/*  73 */         String updateSQL = "UPDATE T_Customer_Info SET LastNoteId = ?, LastContactDate = ?, NextContactDate = ?, UpdateDate = NOW(), UpdateUserId = ? WHERE CustomerId = ? ";
/*  74 */         List params = new ArrayList();
/*  75 */         params.add(Integer.valueOf(id));
/*  76 */         params.add(noteDate);
/*  77 */         params.add(nextContactDate);
/*  78 */         params.add(userId);
/*     */ 
/*  80 */         params.add(Integer.valueOf(subjectId));
/*  81 */         if (subjectType == 1) {
/*  82 */           updateSQL = updateSQL + " OR CustomerId = ? ";
/*     */ 
/*  84 */           params.add(Integer.valueOf(parentCustomerId));
/*     */         }
/*  86 */         runner.update(conn, updateSQL, params.toArray());
/*     */       }
/*  88 */       if (dealId > 0)
/*     */       {
/*  91 */         int num = runner.update(conn, 
/*  92 */           "UPDATE T_Deal_Info SET LastContactDate = ?, UpdateDate = NOW() WHERE DealId = ?", new Object[] { noteDate, Integer.valueOf(dealId) });
/*  93 */         if ((num > 0) && (subjectType == 2))
/*     */         {
/*  95 */           runner.update(conn, 
/*  96 */             "UPDATE T_Customer_Info SET LastContactDate = ?, UpdateDate = NOW() WHERE CustomerId IN (SELECT CustomerId FROM T_Customer_Deal_Info WHERE DealId = ?)", new Object[] { 
/*  97 */             noteDate, Integer.valueOf(dealId) });
/*     */         }
/*     */       }
/* 100 */       return id;
/*     */     } catch (SQLException e) {
/* 102 */       logger.warn("add note failed", e);
/*     */     } finally {
/*     */       try {
/* 105 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 107 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/* 110 */     return 0;
/*     */   }
/*     */ 
/*     */   public int delete(int id)
/*     */     throws DBException, SQLException
/*     */   {
/* 124 */     if (id <= 0) {
/* 125 */       return 0;
/*     */     }
/*     */ 
/* 128 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 129 */     if (conn == null) {
/* 130 */       throw new DBException();
/*     */     }
/*     */ 
/* 133 */     QueryRunner runner = new QueryRunner();
/*     */     try
/*     */     {
/* 136 */       return runner.update(conn, "DELETE FROM T_Note_Info WHERE NoteId = ?", Integer.valueOf(id));
/*     */     }
/*     */     finally
/*     */     {
/*     */       try
/*     */       {
/* 147 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 149 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/*     */   }
/*     */ 
/*     */   public List<Map<String, Object>> listCustomerNotes(String subjectIds, int offset, int pageSize)
/*     */     throws DBException, SQLException
/*     */   {
/* 200 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 201 */     if (conn == null) {
/* 202 */       throw new DBException();
/*     */     }
/*     */ 
/* 205 */     QueryRunner runner = new QueryRunner();
/*     */     try {
/* 207 */       String sql = "SELECT * FROM T_Note_Info WHERE SubjectType < 2 AND SubjectId IN ( " + subjectIds + 
/* 208 */         ") ORDER BY NoteDate DESC , CreateDate DESC";
/*     */ 
/* 211 */       if (offset > -1) {
/* 212 */         sql = sql + " LIMIT " + offset + ", " + pageSize;
/*     */       }
/* 214 */       return (List)runner.query(conn, sql, new MyMapListHandler());
/*     */     } finally {
/*     */       try {
/* 217 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 219 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/*     */   }
/*     */ 
/*     */   public List<Map<String, Object>> listDealNotes(int dealId, boolean isExport)
/*     */     throws DBException, SQLException
/*     */   {
/* 235 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 236 */     if (conn == null) {
/* 237 */       throw new DBException();
/*     */     }
/* 239 */     String sql = isExport ? "SELECT * FROM T_Note_Info WHERE NoteType = 2 AND DealId = ? ORDER BY NoteDate DESC , CreateDate DESC " : 
/* 240 */       "SELECT * FROM T_Note_Info WHERE DealId = ? ORDER BY NoteDate DESC , CreateDate DESC ";
/* 241 */     QueryRunner runner = new QueryRunner();
/*     */     try {
/* 243 */       return (List)runner.query(conn, sql, new MyMapListHandler(), new Object[] { Integer.valueOf(dealId) });
/*     */     } finally {
/*     */       try {
/* 246 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 248 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/*     */   }
/*     */ 
/*     */   public int updateDealNote(int noteId, String remark, String attachFiles, String userId)
/*     */     throws DBException, SQLException
/*     */   {
/* 266 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 267 */     if (conn == null) {
/* 268 */       throw new DBException();
/*     */     }
/*     */ 
/* 271 */     QueryRunner runner = new QueryRunner();
/*     */     try {
/* 273 */       return runner.update(conn, "UPDATE T_Note_Info SET AttachFiles = ?, Remark = ?, UpdateDate = NOW(),UpdateUserId = ? WHERE NoteId = ?", new Object[] { attachFiles, 
/* 274 */         remark, userId, Integer.valueOf(noteId) });
/*     */     } finally {
/*     */       try {
/* 277 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 279 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/*     */   }
/*     */ 
/*     */   public int updateCustomerNote(int noteId, String remark, Date nextContactDate, String attachFiles, String userId)
/*     */     throws DBException, SQLException
/*     */   {
/* 300 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 301 */     if (conn == null) {
/* 302 */       throw new DBException();
/*     */     }
/*     */ 
/* 305 */     QueryRunner runner = new QueryRunner();
/*     */     try {
/* 307 */       int num = runner.update(conn, "UPDATE T_Note_Info SET NextContactDate = ?, AttachFiles = ?, Remark = ?, UpdateDate = NOW(),UpdateUserId = ? WHERE NoteId = ?", new Object[] { 
/* 308 */         nextContactDate, attachFiles, remark, userId, Integer.valueOf(noteId) });
/*     */ 
/* 310 */       if (num > 0) {
/* 311 */         runner.update(conn, 
/* 312 */           "UPDATE T_Customer_Info SET NextContactDate = ?, UpdateDate = NOW(), UpdateUserId = ? WHERE LastNoteId = ? ", new Object[] { 
/* 313 */           nextContactDate, userId, Integer.valueOf(noteId) });
/*     */       }
/* 315 */       return num;
/*     */     } finally {
/*     */       try {
/* 318 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 320 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/*     */   }
/*     */ 
/*     */   public int updateRemark(int noteId, String remark, String userId)
/*     */     throws DBException, SQLException
/*     */   {
/* 337 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 338 */     if (conn == null) {
/* 339 */       throw new DBException();
/*     */     }
/*     */ 
/* 342 */     QueryRunner runner = new QueryRunner();
/*     */     try {
/* 344 */       return runner.update(conn, "UPDATE T_Note_Info SET Remark = ?, UpdateDate = NOW(), UpdateUserId = ? WHERE NoteId = ?", new Object[] { remark, 
/* 345 */         userId, Integer.valueOf(noteId) });
/*     */     } finally {
/*     */       try {
/* 348 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 350 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/*     */   }
/*     */ 
/*     */   public int updateRemarkByCreater(int noteId, String remark, String createUserId)
/*     */     throws DBException, SQLException
/*     */   {
/* 368 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 369 */     if (conn == null) {
/* 370 */       throw new DBException();
/*     */     }
/*     */ 
/* 373 */     QueryRunner runner = new QueryRunner();
/*     */     try {
/* 375 */       return runner.update(conn, "UPDATE T_Note_Info SET Remark = ?, UpdateDate = NOW(), UpdateUserId = ? WHERE NoteId = ? AND CreateUserId = ? ", new Object[] { 
/* 376 */         remark, createUserId, Integer.valueOf(noteId), createUserId });
/*     */     } finally {
/*     */       try {
/* 379 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 381 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/*     */   }
/*     */ 
/*     */   public int searchTotalNum(int companyId, String key, boolean isAdmin, boolean isGroupAdmin, int groupId, String loginUserId)
/*     */     throws DBException, SQLException
/*     */   {
/* 402 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 403 */     if (conn == null) {
/* 404 */       throw new DBException();
/*     */     }
/*     */ 
/* 407 */     List params = new ArrayList();
/* 408 */     QueryRunner runner = new QueryRunner();
/* 409 */     params.add(Integer.valueOf(companyId));
/* 410 */     params.add("%" + key + "%");
/*     */     try {
/* 412 */       String sql = "SELECT COUNT(*) FROM T_Note_Info WHERE NoteType = 2 AND CompanyId = ? AND Remark like ? ";
/* 413 */       if ((!isAdmin) && (!isGroupAdmin)) {
/* 414 */         sql = "SELECT COUNT(*) FROM T_Note_Info AS A INNER JOIN T_Customer_Info AS B ON A.SubjectId = B.CustomerId WHERE B.DeleteFlag = 0 AND A.NoteType = 2 AND A.CompanyId = ? AND A.Remark like ? AND (B.Visible = ? OR (B.Visible = ? AND B.PermissionsId = ?))";
/* 415 */         params.add(Integer.valueOf(1));
/* 416 */         params.add(Integer.valueOf(4));
/* 417 */         params.add(loginUserId);
/* 418 */       } else if (isGroupAdmin) {
/* 419 */         sql = "SELECT COUNT(*) FROM T_Note_Info AS A INNER JOIN T_Customer_Info AS B ON A.SubjectId = B.CustomerId WHERE B.DeleteFlag =0 AND A.NoteType = 2 AND A.CompanyId = ? AND A.Remark like ? AND (B.Visible = ? OR (B.Visible = ? AND B.PermissionsId IN (SELECT UserId FROM T_User_Info WHERE CompanyId = ? AND GroupId = ?)))";
/* 420 */         params.add(Integer.valueOf(1));
/* 421 */         params.add(Integer.valueOf(4));
/* 422 */         params.add(Integer.valueOf(companyId));
/* 423 */         params.add(Integer.valueOf(groupId));
/*     */       }
/*     */ 
/* 426 */       Object obj = runner.query(conn, sql, new ScalarHandler(1), 
/* 427 */         params.toArray());
/* 428 */       if (!(obj instanceof Long)) {
/* 429 */         return 0;
/*     */       }
/* 431 */       return ((Long)obj).intValue();
/*     */     }
/*     */     finally {
/*     */       try {
/* 435 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 437 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/*     */   }
/*     */ 
/*     */   public List<Map<String, Object>> search(int companyId, String key, boolean isAdmin, boolean isGroupAdmin, int groupId, String loginUserId, int offset, int pageSize)
/*     */     throws DBException, SQLException
/*     */   {
/* 461 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 462 */     if (conn == null) {
/* 463 */       throw new DBException();
/*     */     }
/*     */ 
/* 466 */     List params = new ArrayList();
/* 467 */     QueryRunner runner = new QueryRunner();
/* 468 */     params.add(Integer.valueOf(companyId));
/* 469 */     params.add("%" + key + "%");
/*     */     try {
/* 471 */       StringBuffer sql = new StringBuffer("SELECT * FROM T_Note_Info WHERE NoteType = 2 AND CompanyId = ? AND Remark like ? ORDER BY NoteDate DESC, CreateDate DESC ");
/* 472 */       if ((!isAdmin) && (!isGroupAdmin)) {
/* 473 */         sql = new StringBuffer("SELECT A.* FROM T_Note_Info AS A INNER JOIN T_Customer_Info AS B ON A.SubjectId = B.CustomerId WHERE B.DeleteFlag = 0 AND A.NoteType = 2 AND A.CompanyId = ? AND A.Remark like ? AND (B.Visible = ? OR (B.Visible = ? AND B.PermissionsId = ?)) ORDER BY A.NoteDate DESC, A.CreateDate DESC ");
/* 474 */         params.add(Integer.valueOf(1));
/* 475 */         params.add(Integer.valueOf(4));
/* 476 */         params.add(loginUserId);
/* 477 */       } else if (isGroupAdmin) {
/* 478 */         sql = new StringBuffer("SELECT A.* FROM T_Note_Info AS A INNER JOIN T_Customer_Info AS B ON A.SubjectId = B.CustomerId WHERE B.DeleteFlag = 0 AND A.NoteType = 2 AND A.CompanyId = ? AND A.Remark like ? AND (B.Visible = ? OR (B.Visible = ? AND B.PermissionsId IN (SELECT UserId FROM T_User_Info WHERE CompanyId = ? AND GroupId = ?))) ORDER BY A.NoteDate DESC, A.CreateDate DESC ");
/* 479 */         params.add(Integer.valueOf(1));
/* 480 */         params.add(Integer.valueOf(4));
/* 481 */         params.add(Integer.valueOf(companyId));
/* 482 */         params.add(Integer.valueOf(groupId));
/*     */       }
/* 484 */       sql.append(" LIMIT ").append(offset).append(", ").append(pageSize);
/*     */ 
/* 486 */       return (List)runner.query(conn, sql.toString(), new MyMapListHandler(), 
/* 487 */         params.toArray());
/*     */     } finally {
/*     */       try {
/* 490 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 492 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/*     */   }
/*     */ 
/*     */   public int getUserNotesTotalNum(String createUserId)
/*     */     throws DBException, SQLException
/*     */   {
/* 507 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 508 */     if (conn == null) {
/* 509 */       throw new DBException();
/*     */     }
/* 511 */     QueryRunner runner = new QueryRunner();
/*     */     try
/*     */     {
/* 514 */       Object obj = runner.query(conn, "SELECT COUNT(*) FROM T_Note_Info WHERE CreateUserId = ? ", 
/* 515 */         new ScalarHandler(1), new Object[] { createUserId });
/* 516 */       if (!(obj instanceof Long)) {
/* 517 */         return 0;
/*     */       }
/* 519 */       return ((Long)obj).intValue();
/*     */     }
/*     */     finally {
/*     */       try {
/* 523 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 525 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/*     */   }
/*     */ 
/*     */   public List<Map<String, Object>> getUserNotes(String createUserId, int offset, int pageSize)
/*     */     throws DBException, SQLException
/*     */   {
/* 542 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 543 */     if (conn == null) {
/* 544 */       throw new DBException();
/*     */     }
/*     */ 
/* 547 */     QueryRunner runner = new QueryRunner();
/*     */     try {
/* 549 */       StringBuffer sql = new StringBuffer("SELECT * FROM T_Note_Info WHERE CreateUserId = ? ORDER BY NoteDate DESC");
/* 550 */       sql.append(" LIMIT ").append(offset).append(", ").append(pageSize);
/*     */ 
/* 552 */       return (List)runner.query(conn, sql.toString(), new MyMapListHandler(), new Object[] { 
/* 553 */         createUserId });
/*     */     } finally {
/*     */       try {
/* 556 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 558 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/*     */   }
/*     */ 
/*     */   public Map<String, Object> getNoteDetail(int noteId)
/*     */     throws DBException, SQLException
/*     */   {
/* 573 */     Connection conn = ConnectionManager.getInstance().getConnection();
/* 574 */     if (conn == null) {
/* 575 */       throw new DBException();
/*     */     }
/*     */ 
/* 578 */     QueryRunner runner = new QueryRunner();
/*     */     try
/*     */     {
/* 581 */       return (Map)runner.query(conn, "SELECT A.NoteId, A.NoteDate, A.SubjectId, A.Remark, A.CreateUserId, A.CreateDate, B.Name, B.Type FROM T_Note_Info AS A INNER JOIN T_Customer_Info AS B ON A.SubjectId = B.CustomerId WHERE B.DeleteFlag =0 AND A.NoteId = ?", 
/* 582 */         new MyMapHandler(), new Object[] { Integer.valueOf(noteId) });
/*     */     } finally {
/*     */       try {
/* 585 */         DbUtils.close(conn);
/*     */       } catch (SQLException e) {
/* 587 */         logger.warn("close conn failed", e);
/*     */       }
/*     */     }
/*     */   }
/*     */ 
/*     */   public long getTotalNum(Connection conn, int companyId)
/*     */   {
/* 600 */     if (conn == null) {
/* 601 */       return 0L;
/*     */     }
/* 603 */     QueryRunner runner = new QueryRunner();
/*     */     try
/*     */     {
/* 606 */       Object obj = runner.query(conn, 
/* 607 */         "SELECT COUNT(*) FROM T_Note_Info WHERE CompanyId = ? ", 
/* 608 */         new ScalarHandler(1), new Object[] { Integer.valueOf(companyId) });
/* 609 */       if (!(obj instanceof Long)) {
/* 610 */         return 0L;
/*     */       }
/* 612 */       return ((Long)obj).intValue();
/*     */     } catch (SQLException e) {
/* 614 */       logger.warn("get note total num failed", e);
/*     */     }
/* 616 */     return 0L;
/*     */   }
/*     */ }

/* Location:           C:\E\referenceProject\CRM参考\qikebao\WEB-INF\lib\qikebao.jar
 * Qualified Name:     com.newcrm.db.NoteManager
 * JD-Core Version:    0.6.1
 */